package com.fz.us.oa.web.controller.com;

import com.fz.us.admin.company.entity.User;
import com.fz.us.oa.web.controller.BaseController;
import com.fz.us.oa.web.shiro.bind.annotation.CurrentUser;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.Region;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import java.io.*;
import java.net.InetAddress;
import java.net.URLDecoder;
import java.net.UnknownHostException;

/**
 * Created by dzw on 2018/3/22.
 * excel导出
 */
@Controller
@RequestMapping(value = "/com/export")
public class ExportController extends BaseController {
    private static final long serialVersionUID = -2593867795913379288L;

    /**
     * 导出书记统计汇总excel
     */
    @RequestMapping(value = "downloadSj", method = RequestMethod.POST)
    public Object downloadSj(@CurrentUser User user, String fileFullName, String filepath, String sjData) throws IOException {
        String name = "杨浦区居民区党组织换届情况统计表.xls";

        if(StringUtils.isEmpty(sjData)){
            return ajaxJson(resultService.fail("未获取到数据"));
        }

        JSONArray jsonArray = JSONArray.fromObject(sjData);
        File file=new File(URLDecoder.decode(filepath, "UTF-8"));
        String fileName = URLDecoder.decode(fileFullName, "UTF-8");
        OutputStream outputStream = null;
        InputStream inputStream = new FileInputStream(file);
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0); //获取第一张sheet

        for(int i=0;i<jsonArray.size();i++){
            JSONObject data = jsonArray.getJSONObject(i);
            Row row = sheet.createRow(i + 5); //总第6行开始
            row.setHeight((short)500);
            row.createCell(0).setCellValue(data.getString("td0"));
            row.createCell(1).setCellValue(data.getString("td1"));
            row.createCell(2).setCellValue(data.getString("td2"));
            row.createCell(3).setCellValue(data.getString("td3"));
            row.createCell(4).setCellValue(data.getString("td4"));
            row.createCell(5).setCellValue(data.getString("td5"));
            row.createCell(6).setCellValue(data.getString("td6"));
            row.createCell(7).setCellValue(data.getString("td7"));
            row.createCell(8).setCellValue(data.getString("td8"));
            row.createCell(9).setCellValue(data.getString("td9"));
            row.createCell(10).setCellValue(data.getString("td10"));
            row.createCell(11).setCellValue(data.getString("td11"));
            row.createCell(12).setCellValue(data.getString("td12"));
            row.createCell(13).setCellValue(data.getString("td13"));
            row.createCell(14).setCellValue(data.getString("td14"));
            row.createCell(15).setCellValue(data.getString("td15"));
            row.createCell(16).setCellValue(data.getString("td16"));
            row.createCell(17).setCellValue(data.getString("td17"));
            row.createCell(18).setCellValue(data.getString("td18"));
            row.createCell(19).setCellValue(data.getString("td19"));
            row.createCell(20).setCellValue(data.getString("td20"));
            row.createCell(21).setCellValue(data.getString("td21"));
            row.createCell(22).setCellValue(data.getString("td22"));
            row.createCell(23).setCellValue(data.getString("td23"));
            row.createCell(24).setCellValue(data.getString("td24"));
            row.createCell(25).setCellValue(data.getString("td25"));
            row.createCell(26).setCellValue(data.getString("td26"));
            row.createCell(27).setCellValue(data.getString("td27"));
        }

        try {
            outputStream = new FileOutputStream(fileName);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            workbook.write(outputStream);
            if (outputStream != null) {
                outputStream.close();
            }
//            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        File file2=new File(URLDecoder.decode(fileName, "UTF-8"));
        HttpHeaders headers = new HttpHeaders();
//        if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
            fileName = new String(name.getBytes("UTF-8"), "ISO8859-1");
//        }else{
//            fileName = name;
//        }
        headers.setContentDispositionFormData("attachment", fileName);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file2),
                headers, HttpStatus.CREATED);
    }

    /**
     * 导出 主任 统计汇总excel
     */
    @RequestMapping(value = "downloadZr", method = RequestMethod.POST)
    public Object downloadZr(@CurrentUser User user, String fileFullName, String filepath, String zrData) throws IOException {
        String name = "杨浦区居委会主任换届情况统计表.xls";

        if(StringUtils.isEmpty(zrData)){
            return ajaxJson(resultService.fail("未获取到数据"));
        }

        JSONArray jsonArray = JSONArray.fromObject(zrData);
        File file=new File(URLDecoder.decode(filepath, "UTF-8"));
        String fileName = URLDecoder.decode(fileFullName, "UTF-8");
        OutputStream outputStream = null;
        InputStream inputStream = new FileInputStream(file);
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0); //获取第一张sheet

        for(int i=0;i<jsonArray.size();i++){

            if(i==jsonArray.size()-1){ //最后一行的第一列“合计”要合并单元格
                CellRangeAddress region = new CellRangeAddress((short)i+4,(short)i+4,(short)0,(short)1);
                sheet.addMergedRegion(region);
            }

            JSONObject data = jsonArray.getJSONObject(i);
            Row row = sheet.createRow(i + 5); //总第6行开始
            row.setHeight((short) 500);
            row.createCell(0).setCellValue(data.getString("td0"));
            row.createCell(1).setCellValue(data.getString("td1"));
            row.createCell(2).setCellValue(data.getString("td2"));
            row.createCell(3).setCellValue(data.getString("td3"));
            row.createCell(4).setCellValue(data.getString("td4"));
            row.createCell(5).setCellValue(data.getString("td5"));
            row.createCell(6).setCellValue(data.getString("td6"));
            row.createCell(7).setCellValue(data.getString("td7"));
            row.createCell(8).setCellValue(data.getString("td8"));
            row.createCell(9).setCellValue(data.getString("td9"));
            row.createCell(10).setCellValue(data.getString("td10"));
            row.createCell(11).setCellValue(data.getString("td11"));
            row.createCell(12).setCellValue(data.getString("td12"));
            row.createCell(13).setCellValue(data.getString("td13"));
            row.createCell(14).setCellValue(data.getString("td14"));
            row.createCell(15).setCellValue(data.getString("td15"));
            row.createCell(16).setCellValue(data.getString("td16"));
            row.createCell(17).setCellValue(data.getString("td17"));
            row.createCell(18).setCellValue(data.getString("td18"));
            row.createCell(19).setCellValue(data.getString("td19"));
            row.createCell(20).setCellValue(data.getString("td20"));
            row.createCell(21).setCellValue(data.getString("td21"));

        }

        try {
            outputStream = new FileOutputStream(fileName);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            workbook.write(outputStream);
//            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        File file2=new File(URLDecoder.decode(fileName, "UTF-8"));
        HttpHeaders headers = new HttpHeaders();
//        if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
            fileName = new String(name.getBytes("UTF-8"), "ISO8859-1");
//        }else{
//            fileName = fileFullName;
//        }
        headers.setContentDispositionFormData("attachment", fileName);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file2),
                headers, HttpStatus.CREATED);
    }

    /**
     * 导出书记统计表（换届后）
     */
    @RequestMapping(value = "downloadSjAfter", method = RequestMethod.POST)
    public Object downloadSjAfter(String fileFullName, String filepath, String sjData) throws IOException {
        String name = "杨浦区居民区党组织换届情况统计表（换届后）.xls";

        if(StringUtils.isEmpty(sjData)){
            return ajaxJson(resultService.fail("未获取到数据"));
        }

        JSONArray jsonArray = JSONArray.fromObject(sjData);
        File file=new File(URLDecoder.decode(filepath, "UTF-8"));
        String fileName = URLDecoder.decode(fileFullName, "UTF-8");
        OutputStream outputStream = null;
        InputStream inputStream = new FileInputStream(file);
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0); //获取第一张sheet

        for(int i=0;i<jsonArray.size();i++){
            JSONObject data = jsonArray.getJSONObject(i);
            Row row = sheet.createRow(i + 5); //总第6行开始
            row.setHeight((short) 500);
            row.createCell(0).setCellValue(data.getString("td0"));
            row.createCell(1).setCellValue(data.getString("td1"));
            row.createCell(2).setCellValue(data.getString("td2"));
            row.createCell(3).setCellValue(data.getString("td3"));
            row.createCell(4).setCellValue(data.getString("td4"));
            row.createCell(5).setCellValue(data.getString("td5"));
            row.createCell(6).setCellValue(data.getString("td6"));
            row.createCell(7).setCellValue(data.getString("td7"));
            row.createCell(8).setCellValue(data.getString("td8"));
            row.createCell(9).setCellValue(data.getString("td9"));
            row.createCell(10).setCellValue(data.getString("td10"));
            row.createCell(11).setCellValue(data.getString("td11"));
            row.createCell(12).setCellValue(data.getString("td12"));
            row.createCell(13).setCellValue(data.getString("td13"));
            row.createCell(14).setCellValue(data.getString("td14"));
            row.createCell(15).setCellValue(data.getString("td15"));
        }

        try {
            outputStream = new FileOutputStream(fileName);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            workbook.write(outputStream);
            if (outputStream != null) {
                outputStream.close();
            }
            if (outputStream != null) {
                outputStream.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        File file2=new File(URLDecoder.decode(fileName, "UTF-8"));
        HttpHeaders headers = new HttpHeaders();
        fileName = new String(name.getBytes("UTF-8"), "ISO8859-1");
        headers.setContentDispositionFormData("attachment", fileName);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file2),
                headers, HttpStatus.CREATED);
    }

    /**
     * 导出书记汇总统计（换届前后对比）
     */
    @RequestMapping(value = "downloadSjCompare", method = RequestMethod.POST)
    public Object downloadSjCompare(String fileFullName, String filepath, String sjData) throws IOException {
        String name = "杨浦区居民区党组织换届情况统计表（换届前后对比）.xls";

        if(StringUtils.isEmpty(sjData)){
            return ajaxJson(resultService.fail("未获取到数据"));
        }

        JSONArray jsonArray = JSONArray.fromObject(sjData);
        File file=new File(URLDecoder.decode(filepath, "UTF-8"));
        String fileName = URLDecoder.decode(fileFullName, "UTF-8");
        OutputStream outputStream = null;
        InputStream inputStream = new FileInputStream(file);
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0); //获取第一张sheet

        for(int i=0;i<jsonArray.size();i++){
            JSONObject data = jsonArray.getJSONObject(i);
            Row row = sheet.createRow(i + 5); //总第6行开始
            row.setHeight((short)500);
            row.createCell(0).setCellValue(data.getString("td0"));
            row.createCell(1).setCellValue(data.getString("td1"));
            row.createCell(2).setCellValue(data.getString("td2"));
            row.createCell(3).setCellValue(data.getString("td3"));
            row.createCell(4).setCellValue(data.getString("td4"));
            row.createCell(5).setCellValue(data.getString("td5"));
            row.createCell(6).setCellValue(data.getString("td6"));
            row.createCell(7).setCellValue(data.getString("td7"));
            row.createCell(8).setCellValue(data.getString("td8"));
            row.createCell(9).setCellValue(data.getString("td9"));
            row.createCell(10).setCellValue(data.getString("td10"));
            row.createCell(11).setCellValue(data.getString("td11"));
            row.createCell(12).setCellValue(data.getString("td12"));
            row.createCell(13).setCellValue(data.getString("td13"));
            row.createCell(14).setCellValue(data.getString("td14"));
            row.createCell(15).setCellValue(data.getString("td15"));
            row.createCell(16).setCellValue(data.getString("td16"));
            row.createCell(17).setCellValue(data.getString("td17"));
            row.createCell(18).setCellValue(data.getString("td18"));
            row.createCell(19).setCellValue(data.getString("td19"));
            row.createCell(20).setCellValue(data.getString("td20"));
            row.createCell(21).setCellValue(data.getString("td21"));
            row.createCell(22).setCellValue(data.getString("td22"));
            row.createCell(23).setCellValue(data.getString("td23"));
            row.createCell(24).setCellValue(data.getString("td24"));
            row.createCell(25).setCellValue(data.getString("td25"));
            row.createCell(26).setCellValue(data.getString("td26"));
            row.createCell(27).setCellValue(data.getString("td27"));
        }

        try {
            outputStream = new FileOutputStream(fileName);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            workbook.write(outputStream);
            if (outputStream != null) {
                outputStream.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        File file2=new File(URLDecoder.decode(fileName, "UTF-8"));
        HttpHeaders headers = new HttpHeaders();
        fileName = new String(name.getBytes("UTF-8"), "ISO8859-1");
        headers.setContentDispositionFormData("attachment", fileName);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file2),
                headers, HttpStatus.CREATED);
    }

    /**
     * Ceshi 获取信息
     */
    @RequestMapping(value = "getInfo", method = RequestMethod.GET)
    public void getInfo() throws UnknownHostException {
        System.out.println("客户端ip："+request.getRemoteAddr());
        System.out.println("客户端的主机名："+request.getRemoteHost());
        System.out.println("客户端的端口："+request.getRemotePort());
        System.out.println("客户端的用户："+request.getRemoteUser());
        System.out.println("服务器IP ："+request.getLocalAddr());
        System.out.println("服务器端口 ："+request.getLocalPort());

        InetAddress inet = InetAddress.getLocalHost();
        System.out.println("本机的ip=" + inet.getHostAddress());
    }
}
